package com.sapitest.utils.dbconn;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.sapitest.utils.bean.JDBCInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * JDBC工具类，支持MySQL，Oracle
 * @date 2019-01-13
 * @author Liuxu
 * @version 1.0.1
 * @since jdk1.8
 */
public class JDBCUtil {
    private static final Logger logger = LoggerFactory.getLogger(JDBCUtil.class);
    private static Connection connection = null;

    private JDBCUtil(JDBCInfo jdbcInfo){
        DriverManager.setLoginTimeout(jdbcInfo.getLoginTimeout());
        try {
            Class.forName(jdbcInfo.getDriver());
            connection =  DriverManager.getConnection(jdbcInfo.getUrl(), jdbcInfo.getUser(), jdbcInfo.getPassword());
            logger.info("初始化JDBC成功");
        } catch (ClassNotFoundException e) {
            logger.error(e + "找不到类com.sapitest.utils.bean.JDBCInfo");
        } catch (SQLException e) {
            logger.error(e + "  DB error");
        }
    }

    /**
     * 实例化JDBCUtil对象
     * @param driver    数据库驱动
     * @param url   数据库地址
     * @param user  数据库用户
     * @param password   数据库密码
     * @param loginTimeout  连接超时时间
     * @return  Connection
     */
    public static JDBCUtil newInstance(String driver, String url, String user, String password, int loginTimeout){
        JDBCInfo jdbcInfo = new JDBCInfo(driver, url, user, password, loginTimeout);
        return new JDBCUtil(jdbcInfo);
    }

    /**
     * 执行SELECT查询语言，并返回所有的查询结果
     * @param sql   执行的SQL语言，SELECT
     * @return  返回结果以jsonArray格式呈现
     */
    public List<JSONObject> executeQuery(String sql){
        List<JSONObject> arrayList = new ArrayList<>();
        Map<String, String> result = new HashMap<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = connection.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rs.beforeFirst();
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()){
                for(int i=1; i<=rsmd.getColumnCount(); i++){
                    result.put(rsmd.getColumnName(i), rs.getString(i));
                }
                arrayList.add(JSONObject.parseObject(JSON.toJSONString(result)));
            }
            logger.info("执行SQL：" + sql);
            logger.info("执行结果：" + arrayList);
            return arrayList;
        } catch (SQLException e) {
            logger.error(e + "  Execute db error. ");
            return arrayList;
        } finally {
            try {
                if(null != rs)
                    rs.close();
            } catch (SQLException e) {
                logger.error(e + "Close ResultSet error. ");
            }
            try {
                if(null != pstmt)
                    pstmt.close();
            } catch (SQLException e) {
                logger.error(e + "Close PreparedStatement error.");
            }
        }
    }

    /**
     * 执行UPDATE,INSERT,DELETE等数据更新语句
     * @param sql   执行的SQL语言，UPDATE,INSERT,DELETE
     * @return either (1) the row count for SQL Data Manipulation Language (DML) statements
     *         or (2) 0 for SQL statements that return nothing
     */
    public int executeUpdate(String sql){
        PreparedStatement pstmt = null;
        int rs = 0;
        try {
            pstmt = connection.prepareStatement(sql);
            rs = pstmt.executeUpdate();
            logger.info("执行SQL：[" + sql + "]，执行结果：" + rs);
            return rs;
        } catch (SQLException e) {
            logger.error(e + "  Execute db error. ");
            return rs;
        } finally {
            try {
                if(null != pstmt)
                    pstmt.close();
            } catch (SQLException e) {
                logger.error(e + "Close PreparedStatement error.");
            }
        }
    }

    /**
     * 关闭数据库连接
     */
    public void closeConn(){
        try {
            if(null != connection) {
                connection.close();
                logger.info("关闭JDBC连接");
            }
        } catch (SQLException e) {
            logger.error(e + "Close Connection error.");
        }
    }
}
